#pragma once
#include <memory>
#include <list>
#include <set>
#include <vector>
#include "sqlite3.h"
#include "IReading.h"

#include <memory>
#include <exception>
#include <string>
#include <list>
#include <set>
#include <sstream>
#include <iomanip>
#include <vector>
#include "sqlite3.h"
#include "IReading.h"
#include "Reading.h"
#include "ITime.h"
#include "Time.h"


namespace Sensor {

	class DatabaseManager {
	private:
		static const std::string READING_TABLE;
		static const std::string READING_ID;
		static const std::string READING_SENSOR_ID;
		static const std::string READING_TYPE;
		static const std::string READING_TIME;
		static const std::string READING_VALUE;

		sqlite3* database;

		void executeStatement(std::string statement);
		std::string convertTimeToString(const ITime& time) const;
		std::unique_ptr<ITime> convertStringToTime(std::string string) const;
	public:
		DatabaseManager();
		void open(std::string filename, bool overwrite);
		void insertReading(IReading& reading);
		void sampleReadings(std::vector<double> *values, int sensorId, std::string readingType, const ITime& start, const ITime& end, unsigned long int interval) const {
			int result;

			values->clear();
			values->reserve(1024); // FIXME:

			// Format:
			// SELECT time, value FROM readings WHERE sensorId=... AND type=... AND time BETWEEN ... AND ...
			// FIXME: ORDER BY 
			std::ostringstream sql;
			sql << "SELECT " << READING_TIME << ", " << READING_VALUE << " ";
			sql << "FROM " << READING_TABLE << " ";
			sql << "WHERE ";
			sql << READING_SENSOR_ID << "=" << sensorId << " AND ";
			sql << READING_TYPE << "='" << readingType << "' AND ";
			sql << READING_TIME << " BETWEEN '" << convertTimeToString(start) << "' AND '" << convertTimeToString(end) << "'";

			// Convert the SQL to a string.
			// Note: can't use sql.str().c_str() because sql.str() returns
			// a temporary string reference that get deallocated.
			std::string sqlStr = sql.str();

			sqlite3_stmt* statement;
			result = sqlite3_prepare_v2(
				database,
				sqlStr.c_str(),
				-1,
				&statement,
				nullptr
				);

			long int intervalSecond = start.getUnixTime() - interval * 0.5; 

			double sum = 0; // Running sum of samples for current time segment
			int count = 0; // Running sample count for current time segment
			char endTimeStr[20];
			strcpy(endTimeStr, convertTimeToString(Time(intervalSecond)).c_str()); // End time for current time segment in YYYY-MM-DD HH:MM:SS
			std::list<std::unique_ptr<IReading>> readings;
			while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
				const char* timeStr = (const char*)sqlite3_column_text(statement, 0);
				if (strcmp(timeStr, endTimeStr) < 0) {
					double value = sqlite3_column_double(statement, 1);
					sum += value;
					count += 1;
					//printf("%f ", value);
				} else {
					intervalSecond += interval;
					strcpy(endTimeStr, convertTimeToString(Time(intervalSecond)).c_str());
					if (count > 0) {
						values->push_back(sum / count);
					}
					sum = 0;
					count = 0;
				}
			}

			if (result != SQLITE_DONE) {
				throw std::exception("Problem. 1");
			}

			result = sqlite3_finalize(statement);
			if (result != SQLITE_OK) {
				throw std::exception("Problem inserting reading record 3.");
			}
		}
		std::list<std::unique_ptr<IReading>> getReadings(const int sensorId, const std::string readingType, const ITime& start, const ITime& end) const;

		double getReadingAverage(const int sensorId, const std::string readingType, const ITime& start, const ITime& end) const {
			int result;

			// Format:
			// SELECT sensorId, type, time, value FROM readings WHERE sensorId=... AND type=... AND time BETWEEN ... AND ...
			// FIXME: ORDER BY 
			std::ostringstream sql;
			sql << "SELECT " << READING_VALUE << " ";
			sql << "FROM " << READING_TABLE << " ";
			sql << "WHERE ";
			sql << READING_SENSOR_ID << "=" << sensorId << " AND ";
			sql << READING_TYPE << "='" << readingType << "' AND ";
			sql << READING_TIME << " BETWEEN '" << convertTimeToString(start) << "' AND '" << convertTimeToString(end) << "'";

			// Convert the SQL to a string.
			// Note: can't use sql.str().c_str() because sql.str() returns
			// a temporary string reference that get deallocated.
			std::string sqlStr = sql.str();

			std::cout << sqlStr << std::endl;

			sqlite3_stmt* statement;
			result = sqlite3_prepare_v2(
				database,
				sqlStr.c_str(),
				-1,
				&statement,
				nullptr
				);

			double average;
			while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
				average = sqlite3_column_double(statement, 0);
			}

			if (result != SQLITE_DONE) {
				throw std::exception("Problem. 1");
			}

			result = sqlite3_finalize(statement);
			if (result != SQLITE_OK) {
				throw std::exception("Problem inserting reading record 3.");
			}

			return average;
		}

		std::set<int> getSensorIds(const ITime& start, const ITime& end) const;
		std::set<std::string> getReadingTypes() const;
		void close();
	};

}